﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class SalesReportDL
    {
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }




        public static List<SalesReportObj> SalesByBook(DateTime DateStart,DateTime DateEnd)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_Sales_By_Title";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.DateTime;
            param1.Value = DateStart;
            param1.ParameterName = "@dateStart";
            param1.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.DbType = DbType.DateTime;
            param2.Value = DateEnd;
            param2.ParameterName = "@dateEnd";
            param2.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param2);

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<SalesReportObj> lstOfbook = new List<SalesReportObj>();
            SalesReportObj book = null;

            while (dtReader.Read())
            {
                book = new SalesReportObj();
                book.Title = dtReader.GetString(dtReader.GetOrdinal("Title"));
                book.AuthorName = dtReader.GetString(dtReader.GetOrdinal("Author Name"));
                book.TotalSales = dtReader.GetDecimal(dtReader.GetOrdinal("Total Sales"));
                lstOfbook.Add(book);
            }
            return lstOfbook;


        }


        public static List<SalesReportObj> SalesByGenre(DateTime DateStart,DateTime DateEnd)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_Sales_By_Genre";

            SqlParameter param1 = new SqlParameter();
            param1.DbType = DbType.DateTime;
            param1.Value = DateStart;
            param1.ParameterName = "@dateStart";
            param1.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter();
            param2.DbType = DbType.DateTime;
            param2.Value = DateEnd;
            param2.ParameterName = "@dateEnd";
            param2.Direction = ParameterDirection.Input;
            comm.Parameters.Add(param2);

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<SalesReportObj> lstOfGenre = new List<SalesReportObj>();
            SalesReportObj book = null;

            while (dtReader.Read())
            {
                book = new SalesReportObj();
                book.Genre = dtReader.GetString(dtReader.GetOrdinal("Genre"));
                book.TotalSales = dtReader.GetDecimal(dtReader.GetOrdinal("Total Sales"));
                lstOfGenre.Add(book);
            }

            return lstOfGenre;


        }
    }
}
